/* Author: Sheila Tabuena 
   Filename: dbUtilities.js 
   Date Created: MAY-02-2009

  Contains functions to interact with MySQL database via php servlets. Supports both 
  the PABC website notice table, plus a simple table maintenance page. 
  
   Creates an Event Object with method
      EventObject.append(node)-- appends a formated version of the event to "node"
   
    Functions include:
  
    getEventRange(start,end,callBack)--retrieves all the events after 'start' and before 'end' 
                       dates from the events table, passes a callback routine to handle results


    getEventObjects(response)-- turns XML in "response" into a event object array "table", 
                      to make processing easier. returns array "table"
                      
    doCalendarTable()--An example of a callback that getEventRange might use
                      puts database rows contained in responseXML into a global array 
                     'eventArray' and then displays them using append

   makeAndSendQuery(url,callBack,sql) -- sends "sql" to servlet located at "url" via ContentLoader,
                    also supplies "callBack" for successful results
 
   errorFunction()--callback function from ContentLoader for errors, displays error messages

   buildNotTables(callBack)--Builds the notice in the database 
                            (after dropping it if they exist)
   goodBuildFunction()--callback function for successful table creation
 
   addNotice(newNotice,callBack)--adds a new notice "newNotice" to the 
                    notices table, calls "callBack" if successfull
   emptyNotice(callBack)--empties out the notice table (truncate), calls 
                   "callBack" if successful
 
   getNotices(callBack)--retrieves all the notices the notices table, 
                  passes a callback routine to handle results
   getTableArray(response)--turns XML in "response" into a two dimensional array 
                 "table", to make display easier returns array "table"
   refreshNotice()--gets a fresh load from the notices db table and displays it.

   doAdd() -- adds a new announcement to the notices table
   
   addslashes(str)--equivalent function to php addslashes, got it off the web,
               takes argument "str" and returns string with special characters 
               like quotes etc escaped with slashes for mysql
   stripslashes(str)--equivalent function to php stripslashes, got it off the web,
               takes argument "str" and returns string with special characters 
               like quotes etc unescaped by removing the slashes.
	
	displayCalendar() - takes xml response and call fullcalendar.js functions to display calendar
    */

var eventArray ; //Global Variable to hold latest set of events selected from database;

EventObject = function()
{
	this.id = 0;
	this.title = " ";
  this.start = " ";
  this.time = " ";	
  this.speed = 'A';
  this.hills = 1;
  this.miles = 1;
  this.address = " ";
  this.city = " ";
  this.state = "CA";
  this.description = " ";
  this.url = " ";
}

EventObject.prototype.append = function(aNode)
{
  var newNode = jQuery(document.createElement("h4")).appendTo(aNode);
  jQuery(document.createTextNode(this.title)).appendTo(newNode);
  newNode = jQuery(document.createElement("p")).appendTo(aNode);
  var code = "(" + this.speed + "/" + this.hills + "/" + this.miles + ")";
  jQuery(document.createTextNode(code)).appendTo(newNode);
  newNode = jQuery(document.createElement("p")).appendTo(aNode);
  var address = this.address + ", " + this.city + "  " + this.state + "";
  jQuery(document.createTextNode(address)).appendTo(newNode);
  newNode = jQuery(document.createElement("p")).appendTo(aNode);
  jQuery(document.createTextNode(this.description)).appendTo(newNode);
  newNode = jQuery(document.createElement("p")).appendTo(aNode);
  jQuery(document.createTextNode(this.url)).appendTo(newNode);
}

// turns XML in "response" into a event object array "table", to make processing easier
// returns array "table"
function getEventObjects(response)
{
  var evArray = [];
  var rowArray = response.getElementsByTagName("row");
  for (i=0;i<rowArray.length;++i)
  {
  	var colArray = rowArray[i].getElementsByTagName("col");
  	var eObj = new EventObject();
  	eObj.id = colArray[0].firstChild.data;
  	eObj.title = stripslashes(colArray[1].firstChild.data);
  	eObj.start = colArray[2].firstChild.data;
  	eObj.time = colArray[3].firstChild.data;
  	eObj.speed = colArray[4].firstChild.data;
  	eObj.hills = colArray[5].firstChild.data;
  	eObj.miles = colArray[6].firstChild.data;
  	eObj.address = stripslashes(colArray[7].firstChild.data);
  	eObj.city = stripslashes(colArray[8].firstChild.data);
  	eObj.state = colArray[9].firstChild.data;
  	eObj.description = stripslashes(colArray[10].firstChild.data);
  	eObj.url = "http://ctislab.fhda.edu/~hujuli1/bicycle_project/calendar/show_event.php?eventid=" + eObj.id;
    evArray[i] = eObj;
  }
  return evArray;
}

// retrieves all the events after 'start' and before 'end' dates from the events table, 
// passes a callback routine to handle results
function getEventRange(start,end,callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php"
  var sql = "q=SELECT id, title, startDay, startTime, speed, hills, miles, address, city, state, description FROM events";
  sql += " WHERE startDay >= '" + start + "' AND startDay < '" + end + "' ORDER BY startDay, startTime";
  makeAndSendQuery(url,callBack,sql);
}

// retrieves the event given the event id from the events table, 
// passes a callback routine to handle results - Julie Hu
function getEvent(eventId,callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php"
  var sql = "q=SELECT id, title, startDay, startTime, speed, hills, miles, address, city, state, description FROM events";
  sql += " WHERE id = " + eventId;
  makeAndSendQuery(url,callBack,sql);
}

// After getting eventArray from response, show dialog box of event. - Julie Hu
function displayEvent() {
	eventArray = getEventObjects(this.req.responseXML);
console.log('hello');
	var homeNode = $('#home');
console.log('wtf?');	
	divNode = addJqNode(homeNode,"div","showEvent","");
	$('#showEvent').dialog({
		autoOpen: true,
		width: 500,
	});	
	//$('#home').append(setupAddEvent(divNode));
	return false;
}

// An example of a callback that getEventRange might use
// puts database rows contained in responseXML into a global array 
// 'eventArray' and then display it as an html table
function doCalendarTable() 
{
	eventArray = getEventObjects(this.req.responseXML);
  var evNode = document.getElementById("events");
  evNode.innerHTML = "";
  for (i=0;i< eventArray.length; ++ i)
  {
    eventArray[i].append(evNode);
  } 
}

// After getting eventArray from response, call fullcalendar.js to display the calendar with new events. - Julie Hu
function displayCalendar() {
	eventArray = getEventObjects(this.req.responseXML);

	 $('#calendar').fullCalendar({
		draggable: true,
		events: eventArray,
		eventDrop: function(event, delta) {
			alert('"' + event.title + '" has been moved to a new date.' + "\n" + 'Remember to mark your calendars!');
		},
		loading: function(bool) {
			if (bool) $('#loading').show();
			else $('#loading').hide();
		}
	});	

	return 1;
}

function refreshCalendar()
{
	getEventRange("2000-06-01","2020-07-01",doCalendarTable);
}

// Builds the notice table in the database (after dropping them if they exist) 
function buildCalTable()
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/buildCalDB.php"
  var contentLoader = new net.ContentLoader(url,goodCalBuild,errorFunction);
}

// callback function for successful table creation
function goodCalBuild()
{
  var ansNode = document.getElementById("result");
  ansNode.firstChild.data = "Database Table calendar rebuilt";
}


// Builds the notice table in the database (after dropping them if they exist) 
function buildNotTable()
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/buildDB.php"
  var contentLoader = new net.ContentLoader(url,goodNotBuild,errorFunction);
}

// callback function for successful table creation
function goodNotBuild()
{
  var ansNode = document.getElementById("result");
  ansNode.firstChild.data = "Database Table notices rebuilt";
}

// empties out the notice table (truncate), calls "callBack" if successful
function emptyCalendar(callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php"
  var sql = "q=TRUNCATE TABLE events;";
  makeAndSendQuery(url,callBack,sql);
}

// empties out the notice table (truncate), calls "callBack" if successful
function emptyNotice(callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php"
  var sql = "q=TRUNCATE TABLE notices;";
  makeAndSendQuery(url,callBack,sql);
}

// retrieves all the notices the notices table, passes a callback routine to handle results
function getNotices(callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php"
  var sql = "q=SELECT DATE(posted), text FROM notices ORDER BY posted DESC;";
  makeAndSendQuery(url,callBack,sql);
}

// sends "query" to servlet located at "url" via ContentLoader, passes "callBack" for success
function makeAndSendQuery(url,callBack,query) 
{
  var contentLoader = new net.ContentLoader(url,callBack,errorFunction,"POST",query);
}

// callback function from ContentLoader for errors, displays error messages
function errorFunction()
{
  var eNode = document.getElementById("notices");
  addJqNode(eNode,"p","noticeError","ERROR ACCESSING DATABASE: " + this.req.responseText);
}

// turns XML in "response" into a two dimensional array "table", to make display easier
// returns array "table"
function getTableArray(response)
{
  var table = [];
  var rowArray = response.getElementsByTagName("row");
  for (i=0;i<rowArray.length;++i)
  {
  	var colArray = rowArray[i].getElementsByTagName("col");
  	var col = [];
    for (j=0;j<colArray.length;++j)
    {
    	if (colArray[j].firstChild != null)
    	{
         col[j] = colArray[j].firstChild.data;
       }
    }
    table[i] = col;
  }
  return table;
}

// puts database rows contained in responseXML into in a html table
function doNoticeTable() 
{
	currentAnswer = getTableArray(this.req.responseXML);
  var notNode = document.getElementById("notices");
  notNode.innerHTML = "";
  tabNode = addJqNode(notNode,"table","responseTable","");
  for (i=0;i< currentAnswer.length; ++ i)
  {
    trNode = addJqNode(tabNode,"tr",("tr"+i),"");
    for (j=0;j<currentAnswer[i].length; ++j)
    {
    	noticeText = stripslashes(currentAnswer[i][j])
      addJqNode(trNode,"td",("td"+i+j),noticeText);
    }
  } 
}

// adds a new announcement to the notices table
function doAdd() 
{
  var inputNode = document.getElementById("uaInput");
  notice = inputNode.value;
  inputNode.value="";
  if (notice != null & notice.length > 0)
    addNotice(notice,refreshNotice);
}

// adds a new notice "newNotice" to the notices table, calls "callBack" if successfull
function addNotice(newNotice,callBack)
{
	url = "http://ctislab.fhda.edu/~tabuenas/php/anyQuery.php";
  var sql = "q=INSERT INTO notices (text) VALUES ('" + addslashes(newNotice) + "');";
  makeAndSendQuery(url,callBack,sql);
}

// gets a fresh load from the notices db table and displays it.
function refreshNotice()
{
  getNotices(doNoticeTable);
}

/* equivalent function to php addslashes, got it off the web
  takes argument "str" and returns string with special characters 
   like quotes etc escaped with slashes for mysql */

function addslashes(str) 
{
   str=str.replace(/\'/g,'\\\'');
   str=str.replace(/\"/g,'\\"');
   str=str.replace(/\\/g,'\\\\');
   str=str.replace(/\0/g,'\\0');
   return str;
}

/* equivalent function to php stripslashes, got it off the web
   takes argument "str" and returns string with special characters 
   like quotes etc unescaped by removing slashes */
function stripslashes(str) 
{
   str=str.replace(/\\'/g,'\'');
   str=str.replace(/\\"/g,'"');
   str=str.replace(/\\\\/g,'\\');
   str=str.replace(/\\0/g,'\0');
   return str;
}

/* This function is called by dbBike onload, it builds the page, including headers,
   radio buttons, and action buttons.  */
function buildDBPage()
{
  var divNode = document.getElementById("container");
  addNode(divNode,"h1","h1_1","Database Utilities");
//  divNode = addNode(divNode,"div","main","");
  taNode = addNode(divNode,"table","t1","");
  trNode = addNode(taNode,"tr","t2","");
  tdNode = addNode(trNode,"td","t3","");
  
  hNode = addNode(tdNode,"h2","h2_1","Notice Functions: ");
  showNode = addNode(tdNode,"button","getButton","Get Notices");
  showNode.setAttribute("onclick","getNotices(doNoticeTable);");
  emptyNode = addNode(tdNode,"button","emptyButton","Empty Notice Table");
  emptyNode.setAttribute("onclick","emptyNotice(refreshNotice);");
  createNode = addNode(tdNode,"button","createButton","Create Notice Table");
  createNode.setAttribute("onclick","buildNotTable();");
  addNode(tdNode,"h4","h_4","Text for notice: "); 
  addNode(tdNode,"input","uaInput","");
  noticeNode = addNode(tdNode,"button","uaAdd","Add Notice");
  noticeNode.setAttribute("onclick","doAdd();");
  addNode(tdNode,"div","result","Result: ");
  addNode(tdNode,"div","notices","Here are notices");

  tdNode = addNode(trNode,"td","t4","");

  hNode = addNode(tdNode,"h2","h2_2","Calendar Functions: ");
  emptyNode = addNode(tdNode,"button","emptyCButton","Empty Calendar Table");
  emptyNode.setAttribute("onclick","emptyCalender(refreshCalendar);");
  createNode = addNode(tdNode,"button","createCButton","Create Calendar Table");
  createNode.setAttribute("onclick","buildCalTable();");
  showNode = addNode(tdNode,"button","getCButton","Get Calendar Events");
  showNode.setAttribute("onclick","getEventRange('2000-06-01','2020-07-01',doCalendarTable);");
  setupAddEvent(tdNode);
  addNode(tdNode,"div","events","Here are events");
}
